<?php
/**
 * @Description: 任教学科
 * @Author: kzj
 * @datetime: 2023-02-24
 */
namespace App\Services\Teaching;

use App\Http\Controllers\Excel\ExcelExport;
use App\Http\Controllers\Excel\ExcelImport;
use App\Models\DecSubject;
use App\Models\DictionaryItem;
use App\Models\FormInstance;
use App\Models\System\Tasks;
use App\Models\System\Term;
use App\Models\System\User;
use App\Models\Teaching\TeachingSubject;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;

/**
 * 任教学科 (服务)
 */
class TeachingSubjectService
{
    /** @var \App\Repositories\Teaching\TeachingSubjectRepository $teachingSubjectRepository */
    private $teachingSubjectRepository;
    /** @var \App\Repositories\System\DictionaryItemRepository $dictionaryItemRepository */
    private $dictionaryItemRepository;

    private $formKey = 'TEARRESEARCH_TEASUBJECTS_TABLE';

    public function __construct()
    {
        $this->teachingSubjectRepository = app('teachingSubjectRepository');
        $this->dictionaryItemRepository = app('dictionaryItemRepository');
    }


    /**
     * @Description: 任教学科列表信息数据
     * @Author: kzj
     * @datetime: 2022-11-21
     */
    public function getList($request)
    {
        $page = $request['page'] ?? 1;
        $limit = $request['limit'] ?? 10;
        // dd($request);
        $termId = 0;
        $subjectId = 0;
        $gradeId = 0;
        $classtId = 0;
        $name = '';
        $status = '';
        $viewAll = false;
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['subject_dic_items'] )&& !empty($request['search']['subject_dic_items'])){
            $subjectId = $request['search']['subject_dic_items'];
        }
        if (isset($request['search']['grade_dic_items'] )&& !empty($request['search']['grade_dic_items'])){
            $gradeId = $request['search']['grade_dic_items'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $classtId = $request['search']['class_item'];
        }
        if (isset($request['search']['name'] )&& !empty($request['search']['name'])){
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $status = $request['search']['status'];
        }

        $user = Auth::user();
        //非审核权限只能看到自己提交的数据
        if(is_check(212)){
            $viewAll = true;
        }
        $result = TeachingSubject::where('teaching_subject.deleted', 0)->select('teaching_subject.*')
            ->leftJoin('form_instance as b' ,'teaching_subject.form_instance_id','=','b.id')
            ->when($user->hasRole([2]), function($query) use($request,$user){
                $query->whereRaw("((`teaching_subject`.`creator_id` = ".Auth::id().") or (`teaching_subject`.`user_id` = ".Auth::id()." and `b`.`status` != 'FILL_IN'))");
                /**
                $query->whereRaw(function ($q) {
                    $q->where('teaching_subject.creator_id', Auth::id());
                })->orWhere(function ($q) use ($user) {
                    $q->where('teaching_subject.user_id', Auth::id());
                    $q->where('b.status', '!=', 'FILL_IN');
                });**/
            })->when(!$user->hasRole([2]), function($query) use($user){
                $query->whereHas('instance', function ($query) {
                    $query->where('status', '!=','FILL_IN')
                        ->orWhere('teaching_subject.creator_id', Auth::id());
                });
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function (Builder $query) use($termId) {
                    $query->where('id', $termId);
                });
            })
            ->when($subjectId > 0, function($q) use($subjectId) {
                $q->whereRaw(" JSON_EXTRACT(`subject_dic_items`,'$[0].optionId') = $subjectId");
            })
            ->when($gradeId > 0, function($q) use($gradeId) {
                $q->whereRaw(" JSON_EXTRACT(teaching_subject.`grade_dic_items`,'$[0].optionId') = $gradeId");
            })
            ->when($classtId > 0, function($q) use($classtId) {
                $q->whereRaw(" JSON_EXTRACT(teaching_subject.`class_item`,'$[0].optionId') = $classtId");
            })
            ->when($name, function($q) use($name) {
                $q->whereHas('user', function (Builder $query) use($name) {
                    $query->where('nickname', 'like', '%' . $name . '%');
                });
            })
            ->when($status, function($q) use($status) {
                $q->whereHas('instance', function (Builder $query) use($status) {
                    $query->where('status', $status);
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){
                $q->where('user_id', $request['user_id']);
            })
            ->with('term', 'instance:id,status', 'user:id,nickname', 'creator')
            ->orderBy('id', 'desc')
            ->paginate($limit, '*', 'page', $page)
            ->toArray();
        foreach ($result['data'] as &$item){
            if($user->id == $item['creator_id']) {
                $item['is_createor'] = 1;
            }else{
                $item['is_createor'] = 0;
            }
            $item['is_part'] = 0;
            if($item['user_id']) {
                if ($item['user_id'] == $user->id && $item['is_createor'] != 1) {
                    $item['is_part'] = 1;
                }
            }
        }
        return $result;
    }

    /**
     * @Description: 任教学科添加
     * @Author: kzj
     * @datetime: 2023-02-28
     */
    public function add($request)
    {
        if(empty($request)) {
            throw new \Exception('请选择数据');
        }

        $user = Auth::user();
        $term = Term::where('id', $request['data_version_id'])->first();
        $status = $request['status'] == 1 ? 'REVIEW' : 'FILL_IN';

        $dicArr = $this->dictionaryItemRepository->getDictionaryItem([$request['subject_dic_items'], $request['grade_dic_items'], $request['class_item']]);
        // $dicArr = $this->dictionaryItemRepository->getDictionaryItem([$request['subject_dic_items']]);
        // $classDicArr = $this->dictionaryItemRepository->getDictionaryItem($request['class_dic_ids']);
        // $gradeDicArr = $this->dictionaryItemRepository->getDictionaryItem($request['grade_dic_ids']);
        $decSubject = DecSubject::query()->select('id as optionId','name as selectValue','name as selectKey')
            ->where('id',$request['subject_dic_items'])
            ->first();
        $decSubject->parentId = 0;
        $decSubject->order = 0;
        //$subject = $dicArr->where('optionId', $request['subject_dic_items'])->first();
        $grade = $dicArr->where('optionId', $request['grade_dic_items'])->first();
        $class = $dicArr->where('optionId', $request['class_item'])->first();

        unset($request['status']);
        unset($request['class_dic_ids']);
        unset($request['grade_item']);
        unset($request['grade_dic_ids']);
        try{
            DB::beginTransaction();
            //添加状态及审核信息
            $formInstance = FormInstance::create([
                'data_version_id' => $request['data_version_id'],
                'data_version_name' => $term->title,
                'form_key'=> $this->formKey,
                'type' => 'MainForm',
                'owner_id' => $user->id,
                'status' => $status,
                'creator_id' => $user->id,
            ]);

            //添加数据
            $request['creator_id'] = $user->id;
            $request['form_instance_id'] = $formInstance->id;
            $request['subject_dic_items'] = json_encode([$decSubject->toArray()]);
            $request['grade_dic_items'] = json_encode([$grade->toArray()]);
            $request['class_item'] = json_encode([$class->toArray()]);
            // $request['grade_dic_items'] = json_encode($gradeDicArr->toArray());
            // $request['class_item'] = json_encode($classDicArr->toArray());
            $result = TeachingSubject::create($request);

            //添加待办
            if($status == 'REVIEW'){
                Tasks::create([
                    'name' => '【' . $term->title . '】教育教学-任教学科审核',
                    'data_version_id' => $term->id,
                    'form_id' => $result->id,
                    'form_key' => $this->formKey,
                    'start_date' => date('Y-m-d H:i:s'),
                    'end_date' => $request['end_date'],
                    'creator_id' => $user->id,
                    'creator_name' => $user->nickname,
                    'param' => '?id=' . $result->id,
                    'url' => '/teaching/discipline/check',
                    'menu_id'=> 212
                ]);
            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

        return $result;
    }

    /*
     * @Description: 任教学科导入
     * @Author:  Kzj
     * @Date: 2023-11-14 09:40:16
     */
    public function import($request)
    {
        ini_set('memory_limit', '1280M');
		ini_set('max_execution_time', '0');
        $data = Excel::toArray(new ExcelImport(), request()->file('file'));

        $data = $data[0];
        if (count($data) < 2) {
            throw new \Exception('模板不能为空');
        }

        $terms = Term::where('deleted', 0)->get();
        $dictionaryItems = DictionaryItem::select('code', 'item_order as order','id as optionId','parent_id as parentId','item_value as selectKey','item_name as selectValue')
            ->whereIn('code', ['CLASS', 'SUBJECT', 'GRADE'])
            ->get();

        $user = Auth::user();
        $errCount = 0;
        $teachingSubjects = [];
        $now = now();
        foreach ($data as $k => &$v) {
            if ($k > 0) {
                if(!$v[1] || !$v[2] || !$v[8]) {
                    array_push($v, '请完善该行数据或删除这一行空数据再操作！');
                    $errCount++;
                    continue;
                }

                $teacher = User::select('id', 'user_id', 'nickname')->where('user_id', trim($v[1]))->first();
                if (!$teacher) {
                    array_push($v, '该教师不存在，请检查教师编号【'.$v[1].'】！');
                    $errCount++;
                    continue;
                }
                if ($teacher->nickname != $v[0]) {
                    array_push($v, '教师姓名【'.$v[0].'】与系统名称【'.$teacher->nickname.'】不匹配，请检查！');
                    $errCount++;
                    continue;
                }
                $ts['user_id'] = $teacher->id;
                $subjectDicInfo = DecSubject::query()->select('id as optionId','name as selectValue','name as selectKey')
                    ->where('name',$v[2])
                    ->first();

                //$subjectDicInfo
                // = $dictionaryItems->where('code', 'SUBJECT')->where('selectValue', $v[2])->first();

                if (!$subjectDicInfo) {
                    $subjectDicLikeInfo = DecSubject::select('id')
                        ->where('item_name', 'like', '%'.$v[2].'%')
                        ->first();
                    if (!$subjectDicLikeInfo) {
                        array_push($v, '该科目不存在，请检查科目【'.$v[2].'】！');
                        $errCount++;
                        continue;
                    } else {
                        $ts['subject_dic_items'] = json_encode([$subjectDicLikeInfo->toArray()]);
                        $ts['subject_name']     = $v[2] ? $v[2] : "";
                    }
                } else {
                    $subjectDicInfo->parentId = 0;
                    $subjectDicInfo->order = 0;
                    $ts['subject_dic_items'] = json_encode([$subjectDicInfo->toArray()]);
                    $ts['subject_name']     = $v[2] ? $v[2] : "";
                }


                $gradeDicInfo = $dictionaryItems->where('code', 'GRADE')->where('selectValue', $v[3])->first();
                if (!$gradeDicInfo) {
                    array_push($v, '该任教年级不存在，请检查任教年级【'.$v[3].'】！');
                    $errCount++;
                    continue;
                } else {
                    $ts['grade_dic_items'] = json_encode([$gradeDicInfo->toArray()]);
                }

                $classDicInfo = DictionaryItem::select('code', 'item_order as order','id as optionId','parent_id as parentId','item_value as selectKey','item_name as selectValue')
                    ->where('code', 'CLASS')->where('item_name',$v[4])->first();
                if (!$classDicInfo) {
                    array_push($v, '该任教班级不存在，请检查任教班级【'.$v[4].'】！');
                    $errCount++;
                    continue;
                } else {
                    $ts['class_item'] = json_encode([$classDicInfo->toArray()]);
                }

                $term = $terms->where('title', $v[8])->first();
                if (!$term) {
                    array_push($v, '该学期不存在，请检查学期【'.$v[8].'】！');
                    $errCount++;
                    continue;
                }
                $ts['data_version_id'] = $term->id;
                $ts['data_version_name'] = $term->title;

                // 整理数据
                $ts['master']           = $v[5] ? $v[5] : "";
                $ts['deputy_master']    = $v[6] ? $v[6] : "";
                $ts['class_hour']       = $v[7] ? $v[7] : "";
                $ts['start_date']       = excelDateFormat($v[9], 'Y-m-d');
                $ts['end_date']         = excelDateFormat($v[10], 'Y-m-d');
                $ts['creator_id']       = $user->id;
                $ts['is_import']        = 1;
                $ts['created_at']       = $now;
                $ts['updated_at']       = $now;

                $teachingSubjects[] = $ts;
            } else {
                if(
                    !(isset($v[0]) && trim($v[0]) == '任课老师姓名') ||
                    !(isset($v[1]) && trim($v[1]) == '任课老师账号') ||
                    !(isset($v[2]) && trim($v[2]) == '任教学科') ||
                    !(isset($v[3]) && trim($v[3]) == '任教年级') ||
                    !(isset($v[4]) && trim($v[4]) == '任教班级') ||
                    !(isset($v[5]) && trim($v[5]) == '任教班主任') ||
                    !(isset($v[6]) && trim($v[6]) == '任教副班主任') ||
                    !(isset($v[7]) && trim($v[7]) == '任教课时') ||
                    !(isset($v[8]) && trim($v[8]) == '所属学期') ||
                    !(isset($v[9]) && trim($v[9]) == '任教开始时间') ||
                    !(isset($v[10]) && trim($v[10]) == '任教结束时间')
                ) {
                    throw new \Exception('模板错误，请上传正确的模板！');
                }

                array_push($v, '错误提示');
            }
        }
        // dd($errCount, $teachingSubjects, $data);
        $result = [
            'is_fail' => 1, //1-成功 0-失败
            'msg' => '导入成功',
            'file_name' => ''
        ];
        if ($errCount == 0) {
            DB::beginTransaction();
            try {
                foreach($teachingSubjects as &$teachingSubject) {
                    $formInstance = FormInstance::create([
                        'data_version_id' => $teachingSubject['data_version_id'],
                        'data_version_name' => $teachingSubject['data_version_name'],
                        'form_key'=> $this->formKey,
                        'type' => 'MainForm',
                        'owner_id' => $teachingSubject['user_id'],
                        'status' => 'PASS',
                        'creator_id' => $user->id,
                    ]);

                    $teachingSubject['form_instance_id'] = $formInstance->id;
                    unset($teachingSubject['data_version_name']);
                }

                //批量插入导入数据
                TeachingSubject::insert($teachingSubjects);

                DB::commit();
            } catch (\Exception $exception) {
                DB::rollback();
                Log::error($exception->getMessage());
                throw new \Exception('数据提交错误！');
            }

            return $result;
        } else {
            //保存错误文件
            $file_name = md5(auth()->user()->id . time()) . '.xlsx';
            Excel::store(new ExcelExport(array_values($data)), $file_name, 'excel');

            $result['is_fail'] = 0;
            $result['msg'] = '导入失败';
            $result['file_name'] = '/download/excel/' . $file_name;
            return $result;
        }
    }

    /**
     * @Description: 任教学科单条数据
     * @Author: kzj
     * @datetime: 2023-02-28
     */
    public function one($request)
    {
        if(!$request['id']) {
            throw new \Exception('ID参数缺失！');
        }

        $result = TeachingSubject::where('id', $request['id'])->with('creator', 'instance:id,status,comment')->first();

        return $result;
    }

    /**
     * @Description: 任教学科编辑
     * @Author: kzj
     * @datetime: 2023-02-28
     */
    public function edit($request)
    {
        if(!$request['id']) {
            throw new \Exception('ID参数缺失！');
        }

        $status = 'REVIEW';
        $user = Auth::user();
        $term = Term::where('id', $request['data_version_id'])->first();
        $teacherSubject = TeachingSubject::whereId($request['id'])->first();

        $dicArr = $this->dictionaryItemRepository->getDictionaryItem([$request['subject_dic_items'], $request['grade_dic_items'], $request['class_item']]);
        // $dicArr = $this->dictionaryItemRepository->getDictionaryItem([$request['subject_dic_items']]);
        // $classDicArr = $this->dictionaryItemRepository->getDictionaryItem($request['class_dic_ids']);
        // $gradeDicArr = $this->dictionaryItemRepository->getDictionaryItem($request['grade_dic_ids']);

        //$subject = $dicArr->where('optionId', $request['subject_dic_items'])->first();
        $grade = $dicArr->where('optionId', $request['grade_dic_items'])->first();
        $class = $dicArr->where('optionId', $request['class_item'])->first();
        $decSubject = DecSubject::query()->select('id as optionId','name as selectValue','name as selectKey')
            ->where('id',$request['subject_dic_items'])
            ->first();
        $decSubject->parentId = 0;
        $decSubject->order = 0;
        unset($request['class_dic_ids']);
        unset($request['grade_item']);
        unset($request['grade_dic_ids']);
        try{
            DB::beginTransaction();

            //编辑状态及审核信息
            FormInstance::whereId($teacherSubject->form_instance_id)->update([
                'data_version_id' => $request['data_version_id'],
                'data_version_name' => $term->title,
                'status' => $status,
                'updater_id' => $user->id
            ]);

            //编辑数据
            $request['updater_id'] = $user->id;
            $request['subject_dic_items'] = json_encode([$decSubject->toArray()]);
            $request['grade_dic_items'] = json_encode([$grade->toArray()]);
            $request['class_item'] = json_encode([$class->toArray()]);
            // $request['grade_dic_items'] = json_encode($gradeDicArr->toArray());
            // $request['class_item'] = json_encode($classDicArr->toArray());
            $result = TeachingSubject::whereId($request['id'])->update($request);

            //处理有驳回的数据
            Tasks::where('form_key', $this->formKey)->where('form_id', $request['id'])->where('status', 1)->where('type', 3)->update(['status' => 2]);
            // 待办处理
            $task = Tasks::where('form_id', $request['id'])->where('form_key', $this->formKey)->where('status', 1)->first();
            if(!$task) {
                Tasks::create([
                    'name' => '【' . $term->title . '】教育教学-任教学科审核',
                    'data_version_id' => $term->id,
                    'form_id' => $request['id'],
                    'form_key' => $this->formKey,
                    'start_date' => date('Y-m-d H:i:s'),
                    'end_date' => $request['end_date'],
                    'creator_id' => $user->id,
                    'creator_name' => $user->nickname,
                    'param' => '?id=' . $request['id'],
                    'url' => '/teaching/discipline/check',
                    'menu_id'=> 212
                ]);
            } else {
                Tasks::where('id', $task->id)->update([
                    'name' => '【' . $term->title . '】教育教学-任教学科审核',
                    'data_version_id' => $term->id,
                    'form_id' => $request['id'],
                    'form_key' => $this->formKey,
                    'start_date' => date('Y-m-d H:i:s'),
                    'end_date' => $request['end_date'],
                    'creator_id' => $user->id,
                    'creator_name' => $user->nickname,
                    'param' => '?id=' . $request['id'],
                    'url' => '/teaching/discipline/check',
                    'menu_id'=> 212
                ]);
            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

        return $result;
    }

    /**
     * @Description: 任教学科审核
     * @Author: kzj
     * @datetime: 2023-02-28
     */
    public function check($request)
    {
        if(!$request['form_id']) {
            throw new \Exception('参数缺失！');
        }
        if(empty($request['status'])) {
            throw new \Exception('必须勾选审核结果！');
        }
        $user = Auth::user();

        try{
            DB::beginTransaction();

            $result = FormInstance::where('id', $request['form_instance_id'])
                ->update([
                    'status' => $request['status'],
                    'comment' => $request['comment'],
                    'updater_id'=> $user->id,
                ]);

            //修改待办事项
            Tasks::where('form_id', $request['form_id'])->where('form_key', $this->formKey)->update(['status' => 2]);

            //如果是驳回，重新给申报人发起待办事项
            if($request['status']== 'REVIEW_FAIL'){
                $task = Tasks::where('form_key', $this->formKey)->where('form_id', $request['form_id'])->where('type', 1)->orderBy('id', 'desc')->first();

                if($task) {
                    Tasks::create([
                        'name' => $task->name.'驳回',
                        'type' => 3,
                        'data_version_id' => $task['data_version_id'],
                        'form_id' => $task->form_id,
                        'form_key' => $this->formKey,
                        'start_date' => date('Y-m-d H:i:s'),
                        'end_date' => date('Y-m-d H:i:s'),
                        'c_user_id' => $task->creator_id,
                        'c_user_name' => $task->creator_name,
                        'creator_id' => $user->id,
                        'creator_name' => $user->nickname,
                        'param' => $task->param,
                        'url' => '/teaching/discipline/edit',
                        'menu_id' => 211
                    ]);
                }

            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

        return $result;
    }

    /**
     * @Description: 任教学科删除
     * @Author: kzj
     * @datetime: 2023-02-28
     */
    public function del($request)
    {
        if(!$request['id']) {
            throw new \Exception('ID参数缺失！');
        }

        try{
            DB::beginTransaction();

            $result = TeachingSubject::whereId($request['id'])->update(['deleted' => 1]);

            Tasks::where('form_key', $this->formKey)->where('form_id', $request['id'])->where('status', 1)->delete();

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }
        return $result;
    }

    /*
     * @Description: 任教学科导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function export($request)
    {
        // dd($request);
        $termId = 0;
        $subjectId = 0;
        $gradeId = 0;
        $classtId = 0;
        $name = '';
        $status = '';
        $viewAll = false;
        $hasSearch = false;
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['subject_dic_items'] )&& !empty($request['search']['subject_dic_items'])){
            $hasSearch = true;
            $subjectId = $request['search']['subject_dic_items'];
        }
        if (isset($request['search']['grade_dic_items'] )&& !empty($request['search']['grade_dic_items'])){
            $hasSearch = true;
            $gradeId = $request['search']['grade_dic_items'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $hasSearch = true;
            $classtId = $request['search']['class_item'];
        }

        if (isset($request['search']['name'] )&& !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $hasSearch = true;
            $status = $request['search']['status'];
        }
        if(!$hasSearch){
            $status = 'PASS';//导出的数据均为审核通过的
        }

        $user = Auth::user();
        //非审核权限只能看到自己提交的数据
        if(is_check(212)){
            $viewAll = true;
        }

        $output = TeachingSubject::where('teaching_subject.deleted', 0)->select('teaching_subject.*')
            ->leftJoin('form_instance as b' ,'teaching_subject.form_instance_id','=','b.id')
            ->when($user->hasRole([2]), function($query) use($request,$user){
                $query->whereRaw("((`teaching_subject`.`creator_id` = ".Auth::id().") or (`teaching_subject`.`user_id` = ".Auth::id()." and `b`.`status` != 'FILL_IN'))");
            })->when(!$user->hasRole([2]), function($query) use($user){
                $query->whereHas('instance', function ($query) {
                    $query->where('status', '!=','FILL_IN')
                        ->orWhere('teaching_subject.creator_id', Auth::id());
                });
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function (Builder $query) use($termId) {
                    $query->where('id', $termId);
                });
            })
            ->when($subjectId > 0, function($q) use($subjectId) {
                $q->whereRaw(" JSON_EXTRACT(`subject_dic_items`,'$[0].optionId') = $subjectId");
            })
            ->when($gradeId > 0, function($q) use($gradeId) {
                $q->whereRaw(" JSON_EXTRACT(teaching_subject.`grade_dic_items`,'$[0].optionId') = $gradeId");
            })
            ->when($classtId > 0, function($q) use($classtId) {
                $q->whereRaw(" JSON_EXTRACT(teaching_subject.`class_item`,'$[0].optionId') = $classtId");
            })
            ->when($name, function($q) use($name) {
                $q->whereHas('user', function (Builder $query) use($name) {
                    $query->where('nickname', 'like', '%' . $name . '%');
                });
            })
            ->when($status, function($q) use($status) {
                $q->whereHas('instance', function (Builder $query) use($status) {
                    $query->where('status', $status);
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){
                $q->where('teaching_subject.user_id', $request['user_id']);
            })
            ->with('term', 'instance:id,status', 'user:id,nickname', 'creator')
            ->orderBy('teaching_subject.id', 'desc')
            ->get();
        // dd($output->toArray());

        $title = ['学年学期','学科名称','年级','班级','开始日期','结束日期','课时', '创建人', '状态'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            $disStatus = $v->instance ? $v['instance']['status'] : '';
            switch($disStatus) {
                case 'REVOKE': $status = '撤回'; break;
                case 'FILL_IN': $status = '未提交'; break;
                case 'REVIEW': $status = '待审核'; break;
                case 'REVIEW_FAIL': $status = '已驳回'; break;
                case 'PASS': $status = '审核通过'; break;
            }

            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => !empty($v->subject_set) ? $v['subject_set'][0]['selectValue'] : '',
                'grade' => !empty($v->grade_set) ? $v['grade_set']['grades'] : '',
                'class' => !empty($v->class_set) ? $v['class_set']['classes'] : '',
                'start_date' => $v->start_date,
                'end_date' => $v->end_date,
                'class_hour' => $v->class_hour,
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status
            ];

            $exportData[] = $item;
        }
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }
}
